﻿Imports System.Data.SqlClient

Public Class frmSelectProdPolicy

    Sub FillPolicies()
        Try
            If Me.txtClientNo.Text.Trim.Length > 0 Then
                Me.Cursor = Cursors.WaitCursor

                'Check filtering condition
                Dim StrCond As String = ""

                'Check Stats (Active, Expired)
                If Me.RActive.Checked = True Then
                    StrCond += " and PeriodTo>=GetDate()"

                ElseIf Me.RExpired.Checked = True Then
                    StrCond += " and PeriodTo<GetDate()"
                End If

                Dim cmd As New SqlCommand("Select SNo,IsNull(InsurComp,N'') InsurComp,IsNull(ClassName,N'') ClassName," & _
                                          "IsNull(AccNo,N'') AccNo,IsNull(PolicyNo,N'') PolicyNo,PeriodFrom,PeriodTo " & _
                                          "From Policies Where ClientNo=" & Me.txtClientNo.Text.Trim & _
                                          " " & StrCond, cnn)
                Dim Reader As SqlDataReader

                Me.GridPolicies.Rows.Clear()

                cnn.Open()
                Reader = cmd.ExecuteReader
                While (Reader.Read)
                    Me.GridPolicies.Rows.Add(New String() {Reader.Item("SNo"), Reader.Item("InsurComp"), Reader.Item("ClassName"), _
                                                           Reader.Item("AccNo"), Reader.Item("PolicyNo"), _
                                                           CDate(Reader.Item("PeriodFrom")).ToString("yyyy/MM/dd"), _
                                                           CDate(Reader.Item("PeriodTo")).ToString("yyyy/MM/dd")})
                End While
                cnn.Close()

                Me.Cursor = Cursors.Default
            End If
        Catch ex As Exception
            Me.Cursor = Cursors.Default
            If cnn.State = ConnectionState.Open Then
                cnn.Close()
            End If
            MsgBox(ex.ToString)
        End Try
    End Sub

    Private Sub frmSelectPolicy_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        SelPolNo = ""
    End Sub

    Private Sub RActive_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RActive.CheckedChanged
        FillPolicies()
    End Sub

    Private Sub RExpired_CheckedChanged(sender As System.Object, e As System.EventArgs) Handles RExpired.CheckedChanged
        FillPolicies()
    End Sub

    Private Sub btnClose_Click(sender As System.Object, e As System.EventArgs) Handles btnClose.Click
        Me.Close()
    End Sub

    Private Sub GridPolicies_CellDoubleClick(sender As System.Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles GridPolicies.CellDoubleClick
        If e.RowIndex > -1 Then
            SelPolNo = Me.GridPolicies.CurrentRow.Cells(0).Value
            Me.Close()
        End If
    End Sub
End Class